﻿CREATE FUNCTION [acms].[GetPostParentSequence]
(
	@PostId int, 
	@PublishDate DateTime
)
RETURNS varchar(30)
AS
BEGIN
	
	declare @ParentId int, @ParentSequence varchar(30)

	Select @ParentId = c.Id, @ParentSequence = c.Sequence
	from acms.Posts a
		inner join acms.PostPositions b on a.Id = b.PostId and (b.PublishDate = @PublishDate or (b.PublishDate is null and @PublishDate is null))
		inner join acms.Posts c on a.ParentGuid = c.PostGuid
		inner join acms.PostPositions d on c.Id = d.PostId and (d.PublishDate = @PublishDate or (d.PublishDate is null and @PublishDate is null))
	where a.Id = @PostId
	
	if(@ParentId is null)
		set @ParentSequence = ''
	else
		set @ParentSequence = isNull(acms.[GetPostParentSequence](@ParentId, @PublishDate), '') + IsNull(@ParentSequence, '0')
	return @ParentSequence
END